package database

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"
	"time"

	"github.com/user/linux-command/models"
	_ "github.com/mattn/go-sqlite3"
)

var DB *sql.DB

// InitDB 初始化数据库
func InitDB() error {
	// 确保数据目录存在
	if err := os.MkdirAll("./data", 0755); err != nil {
		return err
	}
	
	var err error
	DB, err = sql.Open("sqlite3", "./data/mcp_forwarder.db")
	if err != nil {
		return fmt.Errorf("failed to open database: %v", err)
	}

	// 创建表
	err = createTables()
	if err != nil {
		return fmt.Errorf("failed to create tables: %v", err)
	}

	log.Println("Database initialized successfully")
	return nil
}

// createTables 创建数据库表
func createTables() error {
	// 创建MCP服务表
	_, err := DB.Exec(`
		CREATE TABLE IF NOT EXISTS mcp_services (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL UNIQUE,
			url TEXT NOT NULL,
			description TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			is_active BOOLEAN DEFAULT TRUE
		)
	`)
	if err != nil {
		return err
	}

	// 创建MCP工具表
	_, err = DB.Exec(`
		CREATE TABLE IF NOT EXISTS mcp_tools (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			service_id INTEGER NOT NULL,
			name TEXT NOT NULL,
			description TEXT,
			input_schema TEXT,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			FOREIGN KEY (service_id) REFERENCES mcp_services (id) ON DELETE CASCADE,
			UNIQUE(service_id, name)
		)
	`)
	if err != nil {
		return err
	}

	return nil
}

// CreateService 创建MCP服务
func CreateService(service *models.MCPService) error {
	query := `
		INSERT INTO mcp_services (name, url, description, is_active)
		VALUES (?, ?, ?, ?)
	`
	result, err := DB.Exec(query, service.Name, service.URL, service.Description, service.IsActive)
	if err != nil {
		return err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return err
	}

	service.ID = int(id)
	service.CreatedAt = time.Now()
	service.UpdatedAt = time.Now()
	return nil
}

// GetServiceByName 根据名称获取服务
func GetServiceByName(name string) (*models.MCPService, error) {
	var service models.MCPService
	query := `SELECT id, name, url, description, created_at, updated_at, is_active FROM mcp_services WHERE name = ?`
	
	err := DB.QueryRow(query, name).Scan(
		&service.ID, &service.Name, &service.URL, &service.Description,
		&service.CreatedAt, &service.UpdatedAt, &service.IsActive,
	)
	if err != nil {
		return nil, err
	}

	return &service, nil
}

// GetServiceByID 根据ID获取服务
func GetServiceByID(id int) (*models.MCPService, error) {
	var service models.MCPService
	query := `SELECT id, name, url, description, created_at, updated_at, is_active FROM mcp_services WHERE id = ?`
	
	err := DB.QueryRow(query, id).Scan(
		&service.ID, &service.Name, &service.URL, &service.Description,
		&service.CreatedAt, &service.UpdatedAt, &service.IsActive,
	)
	if err != nil {
		return nil, err
	}

	return &service, nil
}

// GetAllServices 获取所有服务
func GetAllServices() ([]models.MCPService, error) {
	query := `SELECT id, name, url, description, created_at, updated_at, is_active FROM mcp_services WHERE is_active = TRUE`
	rows, err := DB.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var services []models.MCPService
	for rows.Next() {
		var service models.MCPService
		err := rows.Scan(
			&service.ID, &service.Name, &service.URL, &service.Description,
			&service.CreatedAt, &service.UpdatedAt, &service.IsActive,
		)
		if err != nil {
			return nil, err
		}
		services = append(services, service)
	}

	return services, nil
}

// CreateTool 创建工具
func CreateTool(tool *models.MCPTool) error {
	// 将inputSchema转换为JSON字符串
	inputSchemaJSON := ""
	if tool.InputSchema != nil {
		jsonBytes, err := json.Marshal(tool.InputSchema)
		if err != nil {
			return err
		}
		inputSchemaJSON = string(jsonBytes)
	}

	query := `
		INSERT INTO mcp_tools (service_id, name, description, input_schema)
		VALUES (?, ?, ?, ?)
	`
	result, err := DB.Exec(query, tool.ServiceID, tool.Name, tool.Description, string(inputSchemaJSON))
	if err != nil {
		return err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return err
	}

	tool.ID = int(id)
	tool.CreatedAt = time.Now()
	return nil
}

// GetToolByName 根据工具名称获取工具信息
func GetToolByName(name string) (*models.MCPTool, error) {
	var tool models.MCPTool
	var inputSchemaJSON string
	
	query := `
		SELECT t.id, t.service_id, t.name, t.description, t.input_schema, t.created_at
		FROM mcp_tools t
		JOIN mcp_services s ON t.service_id = s.id
		WHERE t.name = ? AND s.is_active = TRUE
	`
	
	err := DB.QueryRow(query, name).Scan(
		&tool.ID, &tool.ServiceID, &tool.Name, &tool.Description,
		&inputSchemaJSON, &tool.CreatedAt,
	)
	if err != nil {
		return nil, err
	}

	// 解析JSON Schema
	var inputSchema interface{}
	if inputSchemaJSON != "" {
		if err := json.Unmarshal([]byte(inputSchemaJSON), &inputSchema); err != nil {
			return nil, err
		}
		tool.InputSchema = inputSchema
	}

	return &tool, nil
}

// GetToolsByService 获取指定服务的所有工具
func GetToolsByService(serviceID int) ([]models.MCPTool, error) {
	query := `SELECT id, service_id, name, description, input_schema, created_at FROM mcp_tools WHERE service_id = ?`
	rows, err := DB.Query(query, serviceID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tools []models.MCPTool
	for rows.Next() {
		var tool models.MCPTool
		var inputSchemaJSON string
		
		err := rows.Scan(
			&tool.ID, &tool.ServiceID, &tool.Name, &tool.Description,
			&inputSchemaJSON, &tool.CreatedAt,
		)
		if err != nil {
			return nil, err
		}

		// 解析JSON Schema
		var inputSchema interface{}
		if inputSchemaJSON != "" {
			if err := json.Unmarshal([]byte(inputSchemaJSON), &inputSchema); err != nil {
				tool.InputSchema = nil
			} else {
				tool.InputSchema = inputSchema
			}
		}
		tools = append(tools, tool)
	}

	return tools, nil
}

// DeleteService 删除服务及其工具
func DeleteService(serviceID int) error {
	_, err := DB.Exec("DELETE FROM mcp_services WHERE id = ?", serviceID)
	return err
}